#!/usr/bin/env python3
#-*- coding:utf-8 -*-

'''将爬取的病毒数据存储到数据库中
需要准备一张表来存储'，表要对应爬取的数据'''

from util import mysql_util

'''存储数据将数据传进去,然后回顾我们的连接数据库操作'''
def storage_mysql(gzbd_data):
    # 操作连接对象和游标对象,不需要查询就不用结果集result
    connection,cursor = None,None
    try:
         # 执行插入操作并提交事务
        connection,cursor=mysql_util.get_connection_cursor()
        for gzbd in gzbd_data:
            data=gzbd["时间"]
            query_sql="select * from coronavirus where date='%s'"%(data)
            insert_sql="insert into coronavirus(date,region,diagnosis,overseas_import,cure,death,therapy,observation) " \
                       "values('%s','%s',%s,%s,%s,%s,%s,%s)"\
                       %(gzbd["时间"],gzbd["地区"],gzbd.get("确诊数",None),gzbd.get("输入数",None),gzbd.get("治愈数",None),
                         gzbd.get("死亡数",None),gzbd.get("隔离数",None),gzbd.get("观察数",None))
             #先检查sql语句是否OK
            # print(query_sql)
            # print(insert_sql)
            insert_sql = insert_sql.replace("None", "null")
            result = mysql_util.execute_query(query_sql, cursor)
            if len(result):
                continue
            mysql_util.execute_insert_update_delete(insert_sql, cursor)
        mysql_util.commit_(connection)
    except Exception as e:
        print(e)
        # 回滚数据
        mysql_util.rollback_(connection)
    finally:
        #关闭连接游标
        mysql_util.close_(connection, cursor)
